<?php

/**
 * Creates DB dump.
 *
 * Usage:
 * <pre>
 *      Yii::import('ext.dumpDB.dumpDB');
 *           $dumper = new dbBackup();
 *           $dumper->getDump();
 * </pre>
 */
class dumpDB
{

        private $constraints;


        /**
         * Dump all tables
         * @param boolean $download - if the generated data is to be sent to browser 
         * @return file|strings 
         */
        public function getDump($download = TRUE)
        {
                ob_start();
                foreach($this->getTables() as $key=>$val)
                        $this->dumpTable($key);
                $result = $this->setHeader();
                $result.= ob_get_contents();
                $result.= $this->getConstraints();
                $result.= $this->setFooter();
                ob_end_clean();
                if($download){
                    header("Expires: Mon, 26 Jul 1997 05:00:00 GMT");
                    header("Cache-Control: no-cache");
                    header("Pragma: no-cache");
                    header("Content-type:application/sql");
                    header("Content-Disposition:attachment;filename=downloaded.sql");
                } 
                return $result;
        }

        /**
         * Generate constraints to all tables
         * @return string 
         */
        private function getConstraints()
        {
            $sql = "--\r\n-- Constraints for dumped tables\r\n--".PHP_EOL.PHP_EOL;
            $first = TRUE;
            foreach ($this->constraints as $key => $value) {
                if($first && count($value[0]) > 0){
                    $sql  .= "--\r\n-- Constraints for table `$key`\r\n--".PHP_EOL.PHP_EOL;
                    $sql .= "ALTER TABLE $key".PHP_EOL;
                }
                if(count($value[0]) > 0){
                    for($i=0; $i<count($value[0]);$i++){
                        if(strpos($value[0][$i], 'CONSTRAINT') === FALSE)
                                $sql .= preg_replace('/(FOREIGN[\s]+KEY)/', "\tADD $1", $value[0][$i]);
                        else
                                $sql .= preg_replace('/(CONSTRAINT)/', "\tADD $1", $value[0][$i]);
                        if($i==count($value[0])-1)
                            $sql .= ";".PHP_EOL;
                        if($i<count($value[0])-1)
                            $sql .=PHP_EOL;
                    }
                }
            }
            
            return $sql;            
        }

                
        /**
         * Set sql file header
         * @return string 
         */
        private function setHeader()
        {
            $header = PHP_EOL."--\n-- foreign key checks, autocomit and start a transaction\n--".PHP_EOL.PHP_EOL;
            $header.="SET FOREIGN_KEY_CHECKS=0;".PHP_EOL;
            $header.="SET SQL_MODE=\"NO_AUTO_VALUE_ON_ZERO\";".PHP_EOL;
            $header.="SET AUTOCOMMIT=0;".PHP_EOL;
            $header.="START TRANSACTION;".PHP_EOL.PHP_EOL;
            $header.="/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;".PHP_EOL;
            $header.="/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;".PHP_EOL;
            $header.="/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;".PHP_EOL;
            $header.="/*!40101 SET NAMES utf8 */;".PHP_EOL;
            
            return $header;
        }
        
        
        /**
         * Set sql file footer
         * @return string 
         */
        private function setFooter()
        {
            $footer =PHP_EOL."SET FOREIGN_KEY_CHECKS=1;".PHP_EOL;
            $footer.="COMMIT;".PHP_EOL.PHP_EOL;
            $footer.="/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;".PHP_EOL;
            $footer.="/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;".PHP_EOL;
            $footer.="/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;".PHP_EOL;
            
            return $footer;
        }

                
        /**
         * Create table dump
         * @param $tableName
         * @return mixed
         */
        private function dumpTable($tableName)
        {
                $db = Yii::app()->db;
                $pdo = $db->getPdoInstance();

                echo PHP_EOL."--\n-- Structure for table `$tableName`\n--".PHP_EOL;
                echo PHP_EOL.'DROP TABLE IF EXISTS '.$db->quoteTableName($tableName).';'.PHP_EOL.PHP_EOL;

                $q = $db->createCommand('SHOW CREATE TABLE '.$db->quoteTableName($tableName).';')->queryRow();
                
                $create_query = $q['Create Table'];

                $pattern = '/CONSTRAINT.*|FOREIGN[\s]+KEY/';
                
                // constraints to $tablename
                preg_match_all($pattern, $create_query,$this->constraints[$tableName]);
                
                $create_query = explode(',',preg_replace($pattern, '', $create_query));
                
                for($i=0;$i<count($create_query)-1;$i++){
                    echo ($i>0 && $i<count($create_query)-2)?$create_query[$i].',':$create_query[$i];
                }
                    echo "\n".trim($create_query[$i]).PHP_EOL;
                

                $rows = $db->createCommand('SELECT * FROM '.$db->quoteTableName($tableName).';')->queryAll();

                    
                if(empty($rows))
                        return;
    
                echo PHP_EOL."--\n-- Data for table `$tableName`\n--".PHP_EOL.PHP_EOL;

                $attrs = array_map(array($db, 'quoteColumnName'), array_keys($rows[0]));
                echo 'INSERT INTO '.$db->quoteTableName($tableName).''." (", implode(', ', $attrs), ') VALUES'.PHP_EOL;
                $i=0;
                $rowsCount = count($rows);
                foreach($rows AS $row)
                {
                        // Process row
                        foreach($row AS $key => $value)
                        {
                                if($value === null)
                                        $row[$key] = 'NULL';
                                else
                                        $row[$key] = $pdo->quote($value);
                        }

                        echo " (", implode(', ', $row), ')';
                        if($i<$rowsCount-1)
                                echo ',';
                        else
                                echo ';';
                        echo PHP_EOL;
                        $i++;
                }
                echo PHP_EOL;
                echo PHP_EOL;
        }



               
        /**
         * Get mysql tables list
         * @return array
         */
        private function getTables()
        {
                $db = Yii::app()->db;
                return $db->getSchema()->getTables();
        }
}